﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Net;
using System.Collections.Specialized;
using System.Configuration;
using System.Xml;
namespace ContentMigrator
{
    public partial class frmMetabase : Form
    {
        private IDbConnection mcn;
        private bool mbLoading = false;
        private SPWeb moCurrentWeb;
        private SPList moCurrentLib;
        private SPWeb moRootWeb;
        private DataView dv;
        private enum Criteria { All, ParentSite, ContentType, Category, DateCreated };
        private enum ConnectType { SQL, Access };
        private ConnectType moConnectType;
        private Hashtable htChoiceFieldValues = new Hashtable();

        private void frmMetabase() {
            InitializeComponent();
        }
        private bool mbAllowUserAdd;



        private void BindCheckedListBox(IDbConnection cn, ListBox mylist, string colname, string filter)
        {
            try
            {
                string sql = String.Format("SELECT DISTINCT {0} FROM Content",colname);
                
                if (filter != string.Empty)
                    sql += " WHERE " + filter;

                DataView dv = GetData(cn, sql,moConnectType);
                mylist.Items.Clear();
                foreach (DataRow myrow in dv.Table.Rows)
                {
                    mylist.Items.Add(myrow[colname].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ERROR: [BindCheckedListBox] " + ex.Message);
            }
        }
        private void BindGroupLists(IDbConnection cn)
        {
            try
            {
                foreach (Control mycontrol in this.gbCriteria.Controls)
                {
                    System.Diagnostics.Debug.WriteLine(mycontrol.GetType().ToString());
                    if (mycontrol.GetType().ToString() == "System.Windows.Forms.CheckedListBox")
                    {

                        if (mycontrol.Name.StartsWith("lstGroup")) 
                        {
                            BindCheckedListBox(cn,(ListBox)mycontrol,mycontrol.Name.Replace("lst",""),string.Empty);
                        }
                    }
                }

            }
            catch (Exception)
            {
                
                throw;
            }
        }
        private string GetRecordCount(IDbConnection cn, string filter)
        {
            try
            {
                string sql = "SELECT COUNT(ID) FROM Content";
                if (filter != string.Empty)
                {
                    sql += " WHERE " + filter;
                }
                DataView dv = GetData(cn, sql,moConnectType);
                return dv.Table.Rows[0][0].ToString();
            }
            catch (Exception ex)
            {

                throw new Exception("ERROR: [GetRecordCount] " + ex.Message);
            }
        }
        private string BuildWhereClause()
        {
            try
            {

                ArrayList arrClause = new ArrayList();
                string dummy = string.Empty;
                foreach (Control mycontrol in this.gbCriteria.Controls)
                {
                    if (mycontrol.GetType().ToString() == "System.Windows.Forms.CheckedListBox")
                    {
                        if (mycontrol.Name.StartsWith("lstGroup")) 
                        {
                            CheckedListBox mylist = (CheckedListBox)mycontrol;
                            if (mylist.CheckedItems.Count > 0)
                            {
                                StringBuilder sbClause = new StringBuilder();
                                string colname = mylist.Name.Replace("lst", "");
                                ArrayList values = new ArrayList();
                                sbClause.Append(colname + " IN (");
                                foreach (object myitem in mylist.CheckedItems) {
                                    values.Add("'" + myitem.ToString() + "'");
                                }
                                string[] criteria = (string[])values.ToArray(dummy.GetType());

                                sbClause.Append(String.Join(",",criteria));
                                sbClause.Append(")");
                                arrClause.Add(sbClause.ToString());
                            }
                        }
                    }
                }

                if (chkDateFilter.Checked == true)
                {
                    arrClause.Add("DateCreated BETWEEN #" + dtpFrom.Text + " 12:00 AM# AND #" + dtpTo.Text + " 11:59 PM#");
                }
                string[] clauses = (string[])arrClause.ToArray(dummy.GetType());

                return String.Join(" AND ", clauses);
                //System.Diagnostics.Debug.WriteLine(String.Join(" AND ", arrClause.ToArray(typeof(string))));
            }
            catch (Exception)
            {
                throw;
            }
        }
        private DataView GetData(IDbConnection cn, string sql,ConnectType myconnecttype)
        {
            try
            {
                IDbCommand cmd = null;
                IDataAdapter da = null;
                switch (myconnecttype)
                {
                    case ConnectType.SQL:
                        cmd = new SqlCommand(sql, (SqlConnection)cn);
                        da = new SqlDataAdapter((SqlCommand)cmd);
                        break;
                    case ConnectType.Access:
                        cmd = new OleDbCommand(sql, (OleDbConnection)cn);
                        da = new OleDbDataAdapter((OleDbCommand)cmd);
                        break;
                    default:
                        break;
                }

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0].DefaultView;
            }
            catch (Exception ex)
            {

                throw new Exception("ERROR: [GetData] " + ex.Message);
            }
        }
        private void lstParentSite_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading == true)
                    UpdateRecordCount();
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        private void UpdateRecordCount()
        {
            try
            {
                string filter = BuildWhereClause();
                string sql = "SELECT * FROM Content";
                if (filter != string.Empty)
                    sql += " WHERE " + filter;
                dv = GetData(mcn,sql,moConnectType);
                lblCount.Text = dv.Table.Rows.Count.ToString() + " matching records";
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        private void lstContentType_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading)
                {
                    BindCheckedListBox(mcn, lstGroup3, "Category", BuildWhereClause());
                    UpdateRecordCount();
                }
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        private void CheckedListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading == true)
                    UpdateRecordCount();
            }
            catch (Exception)
            {

                throw;
            }
        }
        private void dtpFrom_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading == true)
                    UpdateRecordCount();
            }
            catch (Exception)
            {

                throw;
            }
        }
        private void dtpTo_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading == true)
                    UpdateRecordCount();
            }
            catch (Exception)
            {

                throw;
            }
        }
        private void chkDateFilter_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!mbLoading == true)
                    UpdateRecordCount();
            }
            catch (Exception)
            {

                throw;
            }
        }
        private void EnumFolders(SPList mylib)
        {
            try
            {
                StaticMethods.EnumFoldersToComboBox(mylib, cbxFolder);

                //add FolderTree field from metabase
                cbxFolder.Items.Add("@FolderTree");
                   
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        private void ConnectDB(string dbfile) {
            try
            {
                string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbfile + @";Persist Security Info=False;";

                mcn = new OleDbConnection(conString);
                mcn.Open();
                lblDisplay.Text = "FILE: " + dbfile;
            }
            catch (Exception ex)
            {
                throw new Exception("Could not connect to " + dbfile + ":" + ex.Message);
            }
        }
        private void ConnectDB(string server, string catalog, string user, string password)
        {
            try
            {
                string conString = "Server = " + server +
                 "; Database = " + catalog +
                 "; User ID = " + user +
                 "; Password = " + password +
                 "; Trusted_Connection = False;";
                mcn = new SqlConnection(conString);
                mcn.Open();

                lblDisplay.Text = "SQL: " + server + "." +
                                            catalog;
            }
            catch (Exception ex)
            {
                throw new Exception("Could not connect to server " + server + ":" + ex.Message);
            }
        }
        private void frmMetabase_Load(object sender, EventArgs e)
        {
            try
            {
                if (Properties.Settings.Default.DBFile != string.Empty)
                {
                    moConnectType = ConnectType.Access;
                    ConnectDB(Properties.Settings.Default.DBFile);

                }
                else
                {
                    moConnectType = ConnectType.SQL;
                    ConnectDB(Properties.Settings.Default.DBServer, Properties.Settings.Default.DBCatalog, Properties.Settings.Default.DBUser, Properties.Settings.Default.DBPassword);
                }

                //Bind lists
                mbLoading = true;
                BindGroupLists(mcn);
                mbLoading = false;

                UpdateRecordCount();
                //SetupGridDropdownColumn(dv.Table.Columns);

                moRootWeb = StaticMethods.GetWebFromUrl(new Uri(Properties.Settings.Default.RootWeb));
                StaticMethods.EnumSitesToComboBox(moRootWeb,cbxSite);
                StaticMethods.EnumLibrariesToComboBox(moCurrentWeb,cbxLibrary);
                EnumFolders(moCurrentLib);
            }
            catch (Exception ex)
            {
           
                throw new Exception("Startup error:" + ex.Message);
            }
        }
        private void EnumContentTypes(SPList mylib)
        {
            StaticMethods.EnumContentTypesToComboBox(mylib, cbxContentTypes);

            if (cbxContentTypes.Items.Count == 0)
            {
                dgvMetadata.DataSource = null;
                dgvMetadata.Refresh();
            }

            lblContentTypes.Enabled = (cbxContentTypes.Items.Count > 0);
            cbxContentTypes.Enabled = (cbxContentTypes.Items.Count > 0);
            btnUpload.Enabled = (cbxContentTypes.Items.Count > 0);
        }
 
       
        private void PopulateGrid()
        {
            try
            {
                if (cbxContentTypes.SelectedIndex > -1)
                {
                    SPContentType ct = moCurrentLib.ContentTypes[cbxContentTypes.SelectedItem.ToString()];

                    dgvMetadata.Rows.Clear();
                      // List all visible and writeable fields in the content type
                    foreach (SPField field in ct.Fields)
                    {
                        if (!(field.ReadOnlyField || field.Hidden || field.ReadOnlyField || field.InternalName.StartsWith("_")))
                        {
                           dgvMetadata.Rows.Add(field.InternalName);

                           if (field.Type == SPFieldType.Choice || field.Type == SPFieldType.MultiChoice)
                           {
                               ArrayList choices = StaticMethods.GetFieldChoices(field);
                               if (!htChoiceFieldValues.Contains(field.InternalName))
                                   htChoiceFieldValues.Add(field.InternalName,choices);
                               else
                                   htChoiceFieldValues[field.InternalName] = choices;
                           }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error retrieving metadata fields: " + ex.Message);
            }
        }
       

        private void cbxSite_SelectedIndexChanged(object sender, EventArgs e)
        {
            moCurrentWeb = StaticMethods.GetWebFromUrl(new Uri(cbxSite.Text));
            StaticMethods.EnumLibrariesToComboBox(moCurrentWeb,cbxLibrary);
        }
        private void cbxLibrary_SelectedIndexChanged(object sender, EventArgs e)
        {
            moCurrentLib = moCurrentWeb.Lists[cbxLibrary.Text];
            EnumContentTypes(moCurrentLib);
            EnumFolders(moCurrentLib);
        }
 

        private void btnUpload_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder Report = new StringBuilder();
#if DEBUG
                StringBuilder Log = new StringBuilder();
#endif
                string metacol;
                SPFolder folder = null;
           
                Cursor.Current = Cursors.WaitCursor;
                this.Enabled = false;
                progressBar1.Visible = true;
                progressBar1.Value = 0;
                progressBar1.Maximum = dv.Table.Rows.Count;

                // Upload each file with the specified metadata information
                foreach (DataRow item in dv.Table.Rows)
                {
                    try
                    {
#if DEBUG
                Log.AppendLine("Top of main loop");
#endif
                        Hashtable properties = new Hashtable();

                        // Only properties that have been filled in will be added to the new file
                        //foreach (DataRow row in dtMetadata.Rows)
#if DEBUG
                        Log.AppendLine("Before metadata loop");
#endif
                        for (int i = 1; i < dgvMetadata.Rows.Count; i++)
                        {
#if DEBUG
                            Log.AppendLine("Top of metadata loop");
#endif
                            DataGridViewRow row = dgvMetadata.Rows[i];
#if DEBUG
                            Log.AppendLine("row set");
#endif
                            if (row.Cells["Value"].Value != null)
                            {
#if DEBUG
                                Log.AppendLine("Metadata value not empty:" + row.Cells["Value"].Value.ToString());
#endif
                                if (row.Cells["Value"].Value.ToString().StartsWith("@"))
                                {
#if DEBUG
                                    Log.AppendLine("Use metabase column");
#endif
                                    //use a column from content metabase as the value
                                    metacol = row.Cells["Value"].Value.ToString().Replace("@", "");
#if DEBUG
                                    Log.AppendLine("metacol set");
#endif

                                    //is it a valid column name?
                                    if (dv.Table.Columns.Contains(metacol))
                                    {
                                        properties.Add(row.Cells["Key"].Value.ToString(), item[metacol].ToString());
#if DEBUG
                                        Log.AppendLine("Value added to hashtable");
#endif
                                    }
                                    else
                                    {
                                        throw new Exception("'" + metacol + "' is not a valid metabase column!");
                                    }
                                }
                                else
                                {
                                    //literal value
                                    properties.Add(row.Cells["Key"].Value.ToString(), row.Cells["Value"].Value.ToString());
                                }
                            }
                        }
#if DEBUG
                        Log.AppendLine("After metadata loop");
#endif

                        if (item["filename"] != null)
                        {
                            string path = item["Filename"].ToString();
                            if (path.Contains("10064"))
                            {
                                Console.WriteLine("Hello!");
                            }

                            string filename = string.Empty;
                            byte[] fileContents = StaticMethods.GetFileBytes(path);
#if DEBUG
                            Log.AppendLine("File bytes retrieved: " + fileContents.Length.ToString());
#endif

                            if (path.ToLower().Contains("http"))
                                filename = path.Substring(path.LastIndexOf("/") + 1);
                            else
                                filename = path.Substring(path.LastIndexOf("\\") + 1);

                            //retrieve or create destination folder
                            if (cbxFolder.Text.ToLower() == "<root>")
                            {
                                //use root folder of library
                                folder = moCurrentWeb.GetFolder(moCurrentLib.RootFolder.Url);
#if DEBUG
                                Log.AppendLine("Root folder retrieved");
#endif
                            }
                            else if (cbxFolder.Text.ToUpper() == "@FOLDERTREE")
                            {
                                //get folder structure from metabase field
                                folder = StaticMethods.GetFolderTree(moCurrentWeb,moCurrentLib, item["FolderTree"].ToString(), chkCreateFolder.Checked);
#if DEBUG
                                Log.AppendLine("Folder tree retrieved/created");
#endif
                            }
                            else
                            {
                                //existing subfolder
                                folder = moCurrentWeb.GetFolder(moCurrentLib.RootFolder.ServerRelativeUrl + "/" + cbxFolder.Text);
#if DEBUG
                                Log.AppendLine("Literal folder retrieved");
#endif
                                //folder = moCurrentWeb.GetFolder(moCurrentLib.Folders[cbxFolder.SelectedIndex - 1].Url);
                                if (!folder.Exists)
                                    throw new Exception("Could not connect to folder '" + cbxFolder.SelectedText + "'");
                            }


                            //add file to library
                            SPFile myfile = folder.Files.Add(filename, fileContents, chkOverwrite.Checked);
                            if (!myfile.Exists)
                                throw new Exception("File not added to folder");
#if DEBUG
                            Log.AppendLine("File added to folder");
                            Log.AppendLine("Setting metadata fields as follows:");
#endif
                            //fill metadata fields
                            foreach (string mykey in properties.Keys)
                            {
#if DEBUG
                                Log.AppendLine(mykey + "=" + properties[mykey].ToString());
#endif
                                myfile.Item[mykey] = properties[mykey].ToString();
                            }
                            //update fields
                            myfile.Item.Update();
#if DEBUG
                            Log.AppendLine("File metadata fields updated");
#endif
                        }
                    }
                    catch (Exception ex)
                    {
#if DEBUG
                        string logfile = System.Environment.CurrentDirectory + "\\ImportLog_" + DateTime.Now.ToString("MMddyyhhmmss") + ".txt";
                        if (File.Exists(logfile) == true) {
                            File.Delete(logfile);
                        }
                        File.WriteAllText(logfile, Log.ToString());
                        MessageBox.Show("DEBUG BUILD: Fatal error. Check the log file " + logfile + ".\nApplication will now terminate.", "DEBUG: ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        mcn.Close();
                        this.Close();
#endif
                        Report.AppendLine(item["Filename"].ToString() + "\t" + ex.Message);
                    }
                    progressBar1.Value += 1;
                    progressBar1.Refresh();
                    this.Refresh();
                }

                Cursor.Current = Cursors.Default;
                this.Enabled = true;

                if (Report.ToString() != string.Empty) {

                    string errfile = System.Environment.CurrentDirectory + "\\ImportErrors_" + DateTime.Now.ToString("MMddyyhhmmss") + ".txt";
                    if (File.Exists(errfile) == true) {
                        File.Delete(errfile);
                    }
                    File.WriteAllText(errfile, Report.ToString());
                    MessageBox.Show("Completed with errors:\nSee " + errfile + " for details.","COMPLETED WITH ERRORS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
                else
                {
                    MessageBox.Show("Completed.","COMPLETED", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
  
                progressBar1.Visible = false;
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;
                this.Enabled = true;
                progressBar1.Visible = false;
                MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            
        }

        private void cbxContentTypes_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (mcn.State == ConnectionState.Open)
            {
                StaticMethods.EnumContentTypeFieldsToComboBox(moCurrentLib, cbxContentTypes.SelectedText, dgvMetadata, htChoiceFieldValues);
            }
                
        }
        void dgvMetadata_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
        }

        void dgvMetadata_CellEnter(object sender, DataGridViewCellEventArgs e)
        {

        }
        void BindComboToArrayList(ComboBox mycombo, ArrayList myarray)
        {
            try
            {
                mycombo.Items.Clear();
                foreach (string myval in myarray)
                {
                    mycombo.Items.Add(myval);
                }
            }
            catch (Exception ex)
            {
                
                throw new Exception("BindComboToArrayList:" + ex.Message);
            }
        }
        void dgvMetadata_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            try
            {
                if (e.Control.GetType() == typeof(DataGridViewComboBoxEditingControl))
                {
                    //get reference to editing control
                    DataGridViewComboBoxEditingControl cbo = e.Control as DataGridViewComboBoxEditingControl;

                    //if we're editing a row that represents a choice-type column, we want
                    //to 
                    //1. load cmbChoiceEditor with the appropriate values
                    //2. position and size cmbChoiceEditor
                    //3. hide the normal editing control
                    //4. show cmbChoiceEditor

               
                    string metacol = dgvMetadata.Rows[dgvMetadata.CurrentRow.Index].Cells["Key"].Value.ToString();
                    System.Diagnostics.Debug.WriteLine(dgvMetadata.CurrentRow.Index);
                    System.Diagnostics.Debug.WriteLine(metacol);
                    if (htChoiceFieldValues.Contains(metacol))
                    {
                        StaticMethods.SetupGridDropdownColumn((ArrayList)htChoiceFieldValues[metacol],cbo);
                        //do not allow user to add their own value
                        cbo.DropDownStyle = ComboBoxStyle.DropDownList;
                        mbAllowUserAdd = false;
                        cbo.Validating += new CancelEventHandler(cbo_Validating);
                    }
                    else
                    {
                        StaticMethods.SetupGridDropdownColumn(dv.Table.Columns,cbo);
                        //allow user to add their own value
                        cbo.DropDownStyle = ComboBoxStyle.DropDown;
                        mbAllowUserAdd = true;
                        cbo.Validating += new CancelEventHandler(cbo_Validating);
                    }
                }
            }
            catch (Exception ex)
            {

                throw new Exception("dgvMetadata_EditingControlShowing:" + ex.Message);
            }
            
        }
        void cbo_Validating(object sender, CancelEventArgs e)
        {

            try
            {
                DataGridViewComboBoxEditingControl cbo = sender as DataGridViewComboBoxEditingControl;

                DataGridView grid = cbo.EditingControlDataGridView;
                DataGridViewComboBoxColumn cboCol = grid.Columns[grid.CurrentCell.ColumnIndex] as DataGridViewComboBoxColumn;
                object value = cbo.Text;


                if (mbAllowUserAdd)
                {
                    // Add value to list if not there


                    if (cbo.Items.IndexOf(value) == -1)
                    {

                        

                        // Must add to both the current combobox as well as the template, to avoid duplicate entries...


                        cbo.Items.Add(value);


                    }
                }
                grid.CurrentCell.Value = value;
                cboCol.Items.Add(value);
            }
            catch (Exception)
            {
                
            }


        }

    }
}
